package com.dullong.firedocmanager.service;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;

import com.dullong.firedocmanager.bean.Role;
import com.dullong.firedocmanager.service.base.BaseServiceSupport;
import com.dullong.firedocmanager.util.ConnectionUtil;

@Service
public class RoleServiceSupport implements RoleService {

	@Override
	public Integer save(String title, Integer parentid) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			return runner.update(conn,
					"insert into role(title,parent) values(?,?)", title,
					parentid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return 0;

	}

	@Override
	public Integer update(String title, Integer parentid, Integer id) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			return runner.update(conn,
					"update role set title=?,parent=? where id=?", title,
					parentid, id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return 0;
	}

	@Override
	public Integer delete(Integer id) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			return runner.update(conn, "delete from role where id=?", id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return 0;

	}

	@Override
	public Role get(Integer id) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			Role role = runner.query(conn,
					"select id,title,parent from role where id=?",
					new BeanHandler<Role>(Role.class), id);
			return role;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return null;
	}

	@Override
	public List<Role> listByParent(Integer parent) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			List<Role> list2 = runner.query(conn,
					"select id,title,parent from role where parent=?",
					new BeanListHandler<Role>(Role.class), parent);
			return list2;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return null;
	}

	@Override
	public List<Role> listRootRole() {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			List<Role> list2 = runner.query(conn,
					"select id,title,parent from role where parent is null",
					new BeanListHandler<Role>(Role.class));
			return list2;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return null;
	}

	@Override
	public List<Role> listAll() {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			List<Role> list2 = runner.query(conn,
					"select id,title,parent from role",
					new BeanListHandler<Role>(Role.class));
			return list2;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return null;
	}

	@Override
	public List<Role> getByUser(String username) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			List<Role> list2 = runner
					.query(conn,
							"SELECT role.id,role.title,role.parent FROM	authority LEFT JOIN role ON roleid = id WHERE authority.username = ? ",
							new BeanListHandler<Role>(Role.class), username);
			return list2;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return null;
	}

	@Override
	public List<Map<String, Object>> getWithStatByUser(String username) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			List<Map<String, Object>> list = runner
					.query(conn,
							"SELECT role.id,role.title,IF (isnull(authority.username),false,true) AS checked FROM	role LEFT OUTER JOIN (SELECT authority.username,authority.roleid FROM authority WHERE authority.username = ?) AS authority ON role.id = authority.roleid",
							new MapListHandler(), username);
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return null;
	}
}
